# coding: utf-8

from datetime import date, datetime

import xlwt
from sqlalchemy import func, and_, or_

from common.bid_notify.message import MessageNotify
from common.models import model_factory, DemandStatus, ParticStatus
from common.utils import DB, redis_client, yag_client
from common.constant import VoucherStatType, GiveVoucherReason, BID_USER_COUNT
from common.demand_util import demand_winner_count
from common.log import Log
from common.models import UserLevelDisplay

publish_count_mapping = {
    1: 'first',
    2: 'second'
}

demand_status_mapping = {
    DemandStatus.ONGOING.value: 'ongoing',
    DemandStatus.BRONZE_MEDAL_DISCOUNT.value: 'bronze'
}


class StatBase(object):
    model = None
    increase_mapping = dict()
    distinct_mapping = dict()
    redis_prefix = 'user_id'

    @classmethod
    def _get_or_crate(cls, session, stat_date):
        stat = session.query(
            cls.model
        ).filter(
            cls.model.stat_date == stat_date
        ).first()

        if not stat:
            stat = cls.model.create(
                session,
                stat_date=stat_date
            )
        return stat

    def _distinct_stat(self, stat, stat_type, user_id):
        """
        create by ld_wu 2018-07-06
        需要去重的统计数据
        发放人数、使用人数等
        :return:
        """
        attr_name = self.distinct_mapping.get(stat_type)
        if attr_name and user_id:
            # 缓存时长25h
            cache_timeout = 25 * 60 * 60
            redis_key = '{attr_name}_{prefix}_{date}'.format(
                attr_name=attr_name,
                date=stat.stat_date,
                prefix=self.redis_prefix
            )
            redis_client.sadd(redis_key, user_id)
            if redis_client.ttl(redis_key) == -1:
                redis_client.expire(redis_key, cache_timeout)
            count = redis_client.scard(redis_key)
            setattr(stat, attr_name, count)

    def _increase_stat(self, stat, stat_type, stat_cnt):
        """
        create by ld_wu 2018-07-05
        持续增长的统计数据
        :return:
        """
        attr_name = self.increase_mapping.get(stat_type)
        if attr_name:
            attr = getattr(stat, attr_name)
            setattr(stat, attr_name, attr + stat_cnt)


class VoucherStat(object):
    """
    create by ld_wu 2018-07-06
    代金券日统计
    """
    model = model_factory.voucher_daily_stat

    increase_mapping = dict(
        [
            (
                '{}_{}'.format(voucher_type.value, reason.value),
                reason.stat_key(voucher_type)
            )
            for reason in GiveVoucherReason
            for voucher_type in VoucherStatType
        ]
    )
    distinct_mapping = dict(
        [
            (
                '{}_{}'.format(voucher_type.value, reason.value),
                reason.stat_user_key(voucher_type)
            )
            for reason in GiveVoucherReason
            for voucher_type in VoucherStatType]
    )

    @classmethod
    def _get_or_crate(cls, session, stat_date, stat_type):
        stat = session.query(
            cls.model
        ).filter(
            cls.model.stat_date == stat_date,
            cls.model.stat_type == stat_type
        ).first()

        if not stat:
            stat = cls.model.create(
                session,
                stat_date=stat_date,
                stat_type=stat_type
            )
        return stat

    def _increase_stat(self, session, stat_date, stat_type, stat_cnt):
        """
        create by ld_wu 2018-07-19
        代金券张数
        """
        if stat_type:
            stat = self._get_or_crate(session, stat_date, stat_type)
            stat.stat_cnt += stat_cnt

    def _distinct_stat(self, session, stat_date, stat_type, user_id):
        """
        create by ld_wu 2018-07-19
        人数数据
        :return:
        """
        if stat_type and user_id:
            # 缓存时长25h
            cache_timeout = 25 * 60 * 60
            redis_key = '{stat_type}_user_id_{date}'.format(
                stat_type=stat_type,
                date=stat_date
            )
            redis_client.sadd(redis_key, user_id)
            stat = self._get_or_crate(session, stat_date, stat_type)
            if redis_client.ttl(redis_key) == -1:
                redis_client.expire(redis_key, cache_timeout)
            count = redis_client.scard(redis_key)
            stat.stat_cnt = count

    def _total_stat(self, session, stat_date, voucher_stat_type, stat_cnt, user_id):
        """
        create by ld_wu 2018-07-19
        增加总计值
        :return:
        """
        menu = VoucherStatType(voucher_stat_type)
        self._increase_stat(session, stat_date, menu.stat_key, stat_cnt)
        self._distinct_stat(session, stat_date, menu.stat_user_key, user_id)

    def day_stat(self, voucher_stat_type, reason, stat_cnt=1, user_id=None):
        """
        create by ld_wu 2018-07-06
        """
        stat_date = date.today()
        with DB() as session:
            key = '{}_{}'.format(voucher_stat_type, reason)
            increase_type = self.increase_mapping.get(key)
            self._increase_stat(session, stat_date, increase_type, stat_cnt)
            distinct_type = self.distinct_mapping.get(key)
            self._distinct_stat(session, stat_date, distinct_type, user_id)
            self._total_stat(session, stat_date, voucher_stat_type, stat_cnt, user_id)


class BidStockStat(StatBase):
    """
    created by chenhj at 2018/7/17
    汇币存量统计
    """
    model = model_factory.stock_day_stat

    increase_mapping = dict(
        incoming='incoming',
        ret_bid_point='ret_bid_point',
        cost_bid_point='cost_bid_point'
    )

    def day_stat(self, stat_type, stat_cnt):
        """
        create by ld_wu 2018-07-06
        :param stat_type: 统计类型
        :param stat_cnt: 持续增长的值
        """
        stat_date = date.today()
        with DB() as session:
            stat = self._get_or_crate(session, stat_date)
            self._increase_stat(stat, stat_type, stat_cnt)
            self._sync_stock(session, stat)

    @classmethod
    def _sync_stock(cls, session, stat):
        """
        同步今日存量
        :param session:
        :param stat:
        :return:
        """
        pack = model_factory.pack
        stat.stock_bid_point = int(session.query(
            pack
        ).with_entities(
            func.sum(pack.bid_point)
        ).first()[0] or 0)


class DemandStat(StatBase):
    """
    create by ld_wu 2018-07-20
    订单分配流程统计
    """
    model = model_factory.demand_day_stat
    redis_prefix = 'demand_id'

    increase_mapping = dict(
        [
            ('{}_{}'.format(status, publish_count),
             '{}_{}_cnt'.format(status_name, publish_count_name))
            for status, status_name in demand_status_mapping.items()
            for publish_count, publish_count_name in publish_count_mapping.items()
        ]
    )
    distinct_mapping = dict(
        [
            (publish_count, 'ro_{}_cnt'.format(publish_count_name))
            for publish_count, publish_count_name in
            publish_count_mapping.items()
        ]
    )

    def day_stat(self, demand_status, published_count, demand_id, stat_cnt=1):
        """
        create by ld_wu 2018-07-20
        订单分配流程统计
        :return:
        """

        stat_date = date.today()
        with DB() as session:
            stat = self._get_or_crate(session, stat_date)
            stat_type = '{}_{}'.format(demand_status, published_count)
            self._increase_stat(stat, stat_type, stat_cnt)
            self._distinct_stat(stat, published_count, demand_id)


class RenewableStat(DemandStat):
    """
    create by ld_wu 2018-07-20
    订单分配统计（回收数量）
    """
    redis_prefix = 'demand_id'
    distinct_mapping = dict(renewable='renewable_cnt')

    def renewable_stat(self, demand_id):
        """
        回收订单数量
        :param demand_id:
        :return:
        """
        stat_date = date.today()
        with DB() as session:
            stat = self._get_or_crate(session, stat_date)
            self._distinct_stat(stat, 'renewable', demand_id)


class DemandBackend(object):
    """
    created by chenhj at 2018/8/1

    """
    @staticmethod
    def encrypt_mobile(mobile):
        """
        created by chenhj at 2018/8/13

        """
        return mobile[:3] + '****' + mobile[-4:]

    @classmethod
    def lock_handle(cls, demand_id):
        """
        created by chenhj at 2018/8/1
        项目锁定后处理
        """
        with DB() as session:
            if demand_winner_count(demand_id, session) < BID_USER_COUNT:
                # 1. 退返未抢企业的派单点(partic相关数据的is_revert同步需要修改)
                # 2. 通知企业主
                back_ent_ids = session.query(
                    model_factory.partic
                ).filter(
                    model_factory.partic.demand_id == demand_id,
                    model_factory.partic.enterprise_id != 0,
                    model_factory.partic.enterprise_id.notin_(
                        session.query(
                            model_factory.partic
                        ).filter(
                            model_factory.partic.demand_id == demand_id,
                            model_factory.partic.enterprise_id != 0,
                            model_factory.partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(
                            func.distinct(model_factory.partic.enterprise_id)
                        )
                    )
                ).with_entities(
                    func.distinct(model_factory.partic.enterprise_id)
                ).all()
                for ent_id in back_ent_ids:
                    ent = model_factory.enterprises.get_by_id(session, ent_id)
                    ent.leave_dispatch_cnt += 1

                    partics = model_factory.partic.get_list_by_args(
                        session,
                        demand_id=demand_id,
                        enterprise_id=ent.id
                    )
                    for p in partics:
                        p.is_revert = True

                    MessageNotify.lock_return_dispatch_notify(
                        ent.user_id, demand_id, session
                    )

    @classmethod
    def dispatch_demands_export(cls, enterprise_id, start, end, email, nickname):
        """
        created by chenhj at 2018/8/13

        """
        with DB() as session:
            subject = '导出派单数据'
            content = """
                尊敬的用户{nickname}：
                您好！您在万屏汇导出了派单数据明细，详情请查看附件。
                万屏汇-LED显示屏采购安装、维修、租赁等业务信息服务。
                请勿直接回复本邮件。
            """.format(nickname=nickname)
            path = '/tmp/dispatch_data_{}_{}.xls'.format(
                enterprise_id, date.today().strftime('%F')
            )
            book = xlwt.Workbook()
            fields = [
                '序号', '推送时间', '客户手机号', '客户称呼', '订单类型', '室内外',
                '面积', '颜色', '间距', '省份', '城市', '跟进状态', '跟进人'
            ]
            sheet = book.add_sheet('派单数据明细')

            for index, value in enumerate(fields):
                sheet.write(0, index, value)

            if start and end:
                cond = and_(
                    model_factory.partic.enterprise_id == enterprise_id,
                    model_factory.partic.created_at.between(start, end)
                )
            else:
                cond = and_(model_factory.partic.enterprise_id == enterprise_id)

            # 取派单数据
            dispatch_demands_ids = session.query(model_factory.partic).filter(
                cond
            ).with_entities(
                model_factory.partic.demand_id.distinct(),
            )
            demands = session.query(model_factory.demand).filter(
                model_factory.demand.id.in_(dispatch_demands_ids)
            ).order_by(
                model_factory.demand.published_at.desc()
            )

            for index, each_demand in enumerate(demands):
                sheet.write(index+1, 0, index + 1)
                sheet.write(index+1, 4, each_demand.type_display)
                sheet.write(index+1, 5, each_demand.surr_display)
                sheet.write(index+1, 6, '{}㎡'.format(each_demand.size))
                sheet.write(index+1, 7, each_demand.color_display)
                sheet.write(index+1, 8, each_demand.span)
                sheet.write(index+1, 9, each_demand.prov)
                sheet.write(index+1, 10, each_demand.city)

                client = session.query(model_factory.user).filter(
                    model_factory.user.id == each_demand.uid
                ).first()
                sheet.write(index+1, 3, client.nickname)
                enterprise = session.query(model_factory.enterprises).filter(
                    model_factory.enterprises.id == enterprise_id
                ).first()

                demand_partic = session.query(model_factory.partic).filter(
                    model_factory.partic.demand_id == each_demand.id,
                    model_factory.partic.enterprise_id == enterprise_id
                ).join(
                    model_factory.user, model_factory.user.id == model_factory.partic.supplier_id
                ).order_by(
                    model_factory.partic.status.desc()
                ).with_entities(
                    model_factory.partic.created_at.label('created_at'),
                    model_factory.user.nickname.label('nickname'),
                    model_factory.user.mobile.label('mobile'),
                    model_factory.partic.status.label('status'),
                    model_factory.partic.is_revert.label('is_revert'),
                    model_factory.partic.follow_type.label('follow_type'),
                    model_factory.partic.apply_real_number_state.label('apply_real_number_state'),
                ).first()
                sheet.write(index+1, 1, datetime.fromtimestamp(demand_partic.created_at).strftime('%F'))
                if demand_partic.is_revert or demand_partic.status != ParticStatus.SUCCEED.value:
                    if enterprise.level in (30, 40):
                        sheet.write(index + 1, 2, client.mobile)
                    else:
                        sheet.write(index + 1, 2, cls.encrypt_mobile(client.mobile))
                    sheet.write(index+1, 11, '/')
                    sheet.write(index+1, 12, '/')
                else:
                    # 抢单成功
                    # 跟进人信息
                    if demand_partic.follow_type == 2:
                        sheet.write(index + 1, 11, '已跟进')
                    elif demand_partic.follow_type == 1:
                        sheet.write(index + 1, 11, '已跟进(锁定)')
                    sheet.write(index + 1, 12, demand_partic.nickname)
                    # 客户电话(永远显示真实号 来自南向&丽丽)
                    sheet.write(index + 1, 2, client.mobile)

            book.save(path)

            yag_client.send(
                to=email, subject=subject,
                contents=[content, path],
                headers={'From': '万屏汇'}
            )
            yag_client.close()

    @classmethod
    def operation_required_export(cls, start, end, email):
        """
        created by lina at 2018-9-3
        导出运营所需信息
        """
        Log.info('operation_required_export beginning')
        with DB() as session:
            subject = '导出用户统计信息'
            content = """
                您好！您在万屏汇导出了用户信息明细，详情请查看附件。
            """
            path = '/tmp/operation_required_data_{}.xls'.format(
                date.today().strftime('%F')
            )
            book = xlwt.Workbook()
            fields = [
                '手机号', '昵称', '微信昵称', '等级', '省份', '城市', '业务范围',
                '微信关注时间', '微信取关时间', '最近抢单时间', '成就值', '汇币余额',
                '付费金额', '付费次数', '抢单次数', '抢单成功次数', '超时未联系次数',
                '消耗汇币', '退返汇币', '申请退返汇币', '代金券消耗', '代金券抵扣',
                '开通省份'
            ]
            sheet = book.add_sheet('用户统计')

            for index, value in enumerate(fields):
                sheet.write(0, index, value)
            res = session.query(model_factory.user).filter(
                model_factory.user.is_internal == 0,
                model_factory.user.mobile.notin_((
                    '13699899775', '18817877341', '15801577693', '18910632128',
                    '18588290314', '18381665312', '18680308092', '13825187899',
                    '13421376954', '13430607063', '15603053210', '13424710263',
                    '18038064601', '17682342507', '18718925917', '18516234676',
                    '15889797200', '13684988236', '13017671158', '18221772171',
                    '18682115996', '15166841990', '15674601234', '18229460730',
                    '18826416361', '13296874281'
                )),
                model_factory.user.type == 100,
                model_factory.user.is_virt == 0,
                model_factory.user.bind_wx_openid == 1
            ).outerjoin(
                model_factory.wxuser, model_factory.wxuser.openid == model_factory.user.wx_openid
            ).join(model_factory.pack, model_factory.pack.uid == model_factory.user.id
                   ).join(model_factory.user_tags, model_factory.user_tags.user_id == model_factory.user.id
            ).with_entities(
                model_factory.user.id.label('id'),
                model_factory.user.mobile.label('mobile'),
                model_factory.user.nickname.label('nickname'),
                model_factory.user.level.label('level'),
                model_factory.user.wx_openid.label('wx_openid'),
                model_factory.wxuser.nickname.label('wxuser_nickname'),
                model_factory.user.prov.label('prov'),
                model_factory.user.city.label('city'),
                model_factory.user.credit.label('credit'),
                model_factory.user.city.label('city'),
                model_factory.pack.bid_point.label('bid_point'),
                model_factory.user_tags.install.label('install'),
                model_factory.user_tags.repair.label('repair'),
                model_factory.user_tags.rent.label('rent'),
                func.from_unixtime(model_factory.wxuser.subscribe_time).label('subscribe_time'),
            ).all()
            Log.info('find out the information')
            for index, each in enumerate(res):
                bill_res = session.query(model_factory.bill).filter(
                    model_factory.bill.uid == each.id,
                    model_factory.bill.type == 1,
                    model_factory.bill.sub_type.in_((2, 3)),
                    model_factory.bill.status == 200,
                    model_factory.bill.created_at.between(start, end)
                ).with_entities(
                    func.sum(model_factory.bill.amt).label('bill_amt'),
                    func.count(model_factory.bill.id).label('bill_cnt'),
                ).first()
                cancel_attent_time = None
                if not each.wx_openid:
                    cancel_res = session.query(model_factory.cancel_attention_users).filter(
                        model_factory.cancel_attention_users.uid == each.id,
                        model_factory.cancel_attention_users.current_attention == 0
                    ).first()
                    if cancel_res:
                        cancel_attent_time = cancel_res.cancel_attent_time
                # 过滤派单未抢单的工程商
                all_partic_data = session.query(model_factory.partic).filter(
                    model_factory.partic.supplier_id == each.id,
                    model_factory.partic.updated_at.between(start, end),
                    or_(
                        and_(
                            model_factory.partic.status.notin_((0, 1, 300)),
                            model_factory.partic.demand_status.notin_([
                                DemandStatus.MASTER_DISPATCHING.value,
                                DemandStatus.DISPATCHING.value
                            ])
                        ),
                        and_(
                            model_factory.partic.status == ParticStatus.SUCCEED.value,
                            model_factory.partic.demand_status.in_([
                                DemandStatus.MASTER_DISPATCHING.value,
                                DemandStatus.DISPATCHING.value
                            ])
                        )
                    )

                )
                partic_cnt = 0
                partic_success_cnt = 0
                partic_timeout_cnt = 0
                apply_ret_bid_point = 0
                if all_partic_data:
                    for each_partic in all_partic_data:
                        partic_cnt += 1
                        if each_partic.status == 130:
                            partic_timeout_cnt += 1
                        if each_partic.status == 200:
                            partic_success_cnt += 1
                            if each_partic.feedback != 0:
                                apply_ret_bid_point += int(each_partic.offer)

                sum_bid_point = 0
                ret_bid_point = 0
                cost_record_res = session.query(model_factory.bid_cost_record).filter(
                    model_factory.bid_cost_record.user_id == each.id,
                    model_factory.bid_cost_record.cost_type.in_((1, 4, 9, 7, 8, 11, 3, 6, 18)),
                    model_factory.bid_cost_record.created_at.between(start, end)
                )
                voucher_cnt = 0
                voucher_used_bid_point = 0
                if cost_record_res:
                    for each_cost in cost_record_res:
                        if each_cost.cost_type in (1, 4, 9, 7, 8, 11):
                            sum_bid_point += int(each_cost.bid_point)
                        elif each_cost.cost_type in (3, 6):
                            ret_bid_point += int(each_cost.bid_point)
                        else:
                            voucher_used_bid_point += int(each_cost.bid_point)
                            voucher_cnt += 1
                areas = session.query(model_factory.user_to_area).filter(
                    model_factory.user_to_area.user_id == each.id
                ).all()
                provs = []
                if areas:
                    provs = session.query(model_factory.area).filter(
                        model_factory.area.id.in_(tuple({str(x.area_id)[:2] + '0000' for x in areas})),
                        model_factory.area.type == 1
                    ).all()
                prov_names = [x.name for x in provs]
                last_partic_time = session.query(model_factory.partic).filter(
                    model_factory.partic.supplier_id == each.id
                ).order_by(
                    model_factory.partic.created_at.desc()
                ).with_entities(
                    func.from_unixtime(model_factory.partic.created_at).label('last_partic_at'),
                ).first()
                sheet.write(index + 1, 0, each.mobile)
                sheet.write(index + 1, 1, each.nickname)
                sheet.write(index + 1, 2, each.wxuser_nickname)
                if each.level in (0, 10, 20):
                    sheet.write(index + 1, 3, UserLevelDisplay(each.level).display)
                else:
                    sheet.write(index + 1, 3, '无')
                sheet.write(index + 1, 4, each.prov)
                sheet.write(index + 1, 5, each.city)
                sheet.write(index + 1, 6, '{}, {}, {}'.format(
                    '安装' if each.install else '',
                    '维修' if each.repair else '',
                    '租赁' if each.rent else '',
                ))
                sheet.write(index + 1, 7, each.subscribe_time.strftime('%F %T') if each.subscribe_time else '')
                sheet.write(index + 1, 8, cancel_attent_time.strftime('%F %T') if cancel_attent_time else '')
                sheet.write(index + 1, 9, last_partic_time.last_partic_at.strftime('%F %T') if last_partic_time else '')
                sheet.write(index + 1, 10, each.credit)
                sheet.write(index + 1, 11, each.bid_point)
                sheet.write(index + 1, 12, bill_res.bill_amt or 0)
                sheet.write(index + 1, 13, bill_res.bill_cnt)
                sheet.write(index + 1, 14, partic_cnt)
                sheet.write(index + 1, 15, partic_success_cnt)
                sheet.write(index + 1, 16, partic_timeout_cnt)
                sheet.write(index + 1, 17, sum_bid_point)
                sheet.write(index + 1, 18, ret_bid_point)
                sheet.write(index + 1, 19, apply_ret_bid_point)
                sheet.write(index + 1, 20, voucher_cnt * 200)
                sheet.write(index + 1, 21, voucher_used_bid_point)
                sheet.write(index + 1, 22, ','.join(prov_names))
            book.save(path)
            yag_client.send(
                to=email, subject=subject,
                contents=[content, path],
                headers={'From': '万屏汇'}
            )
            yag_client.close()
            Log.info('operation_required_export down')
